/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package scheduler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author John
 */
public class DBHandler {

    //hostname jllscohair.db.11636855.hostedresource.com
    //password Passw0rd132!
    //db name jllscohair 
    private Connection connect = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    public ResultSet readDataBase() throws Exception {
        try {
            //String for database
            String dbURL = "jdbc:mysql://jllscohair.db.11636855.hostedresource.com:3306/jllscohair";

            //This loads the MySql driver
            Class.forName("com.mysql.jdbc.Driver");

            //Loading the database url into the driver manger to establish a connection. Also pass the username and password as parameters
            connect = DriverManager.getConnection(dbURL, "jllscohair", "Passw0rd132!");
            System.out.println("CONNECTION ESTABLISHED");

            // Get the result of an SQL Query. Prints out records from User table. This is a test
            preparedStatement = connect.prepareStatement("select * from USER");
            resultSet = preparedStatement.executeQuery();
            //writeResultSet(resultSet);
            return resultSet;

        } catch (Exception e) {
            System.out.println("Problem with connecting to database. Your internet or the server may be down.");
            throw e;
        } finally {
            //close();
        }
    }
    
    public ResultSet readAdminTable() throws Exception {
        try {
            String dbURL = "jdbc:mysql://jllscohair.db.11636855.hostedresource.com:3306/jllscohair";
            Class.forName("com.mysql.jdbc.Driver");
            
            connect = DriverManager.getConnection(dbURL, "jllscohair", "Passw0rd132!");
            System.out.println("Connection Established");
            
            preparedStatement = connect.prepareStatement("select * from ADMIN");
            resultSet = preparedStatement.executeQuery();
            return resultSet;
        } catch (Exception e) {
            System.out.println("Problem with connecting to database. Your internet or the server may be down.");
            throw e;
        } finally{
        }
        
        }
    

    //Used for printing out information from User Table
    private void writeResultSet(ResultSet resultSet) throws SQLException {
        //Inialize it before the first data set
        while (resultSet.next()) {
            int userID = resultSet.getInt("USER_ID");
            String userFirstName = resultSet.getString("USER_FIRSTNAME");
            String userLastName = resultSet.getString("USER_LASTNAME");
            System.out.println("User id: " + userID);
            System.out.println("User Name: " + userFirstName + userLastName);
        }
    }

    private void close() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null) {
                statement.close();
            }

            if (connect != null) {
                connect.close();
                System.out.println("CONNECTION TERMINATED");
            }
        } catch (Exception e) {
        }
    }

    public static void main(String[] args) throws Exception {
        DBHandler db = new DBHandler();
        db.readDataBase();
    }

    public void addUser(String userName, String pass, String email, String fName, String surname, String userPhone) {
        try {
            String sqlStatement = String.format("INSERT INTO USER (USER_FIRSTNAME, USER_LASTNAME, USER_ALIAS, USER_PASSWORD, USER_EMAIL, USER_PHONE) VALUES ('%s', '%s', '%s', '%s', '%s' , '%s')", fName, surname, userName, pass, email, userPhone);
            //String sqlStatement = "INSERT INTO USER (USER_ID, USER_FIRSTNAME, USER_LASTNAME, USER_ALIAS, USER_PASSWORD, USER_EMAIL) VALUES (3, 'Soo', 'Sohn', 'sysohnn', 'pass', 'sysognatgmail')";
            String dbURL = "jdbc:mysql://jllscohair.db.11636855.hostedresource.com:3306/jllscohair";
            Class.forName("com.mysql.jdbc.Driver");
            connect = DriverManager.getConnection(dbURL, "jllscohair", "Passw0rd132!");
            Statement statement = connect.createStatement();
            statement.executeUpdate(sqlStatement);
        } catch (Exception ex) {
            System.err.println(ex.getMessage());;
        } finally {
            try {
                if (connect != null) {
                    connect.close();
                }
            } catch (SQLException e) {
                System.err.println(e);
            }

        }
    }

    void addAdmin(String userName, String pass, String email, String fName, String surname, String userPhone) {
                try {
                    String sqlStatement = String.format("INSERT INTO ADMIN (ADMIN_FIRSTNAME, ADMIN_LASTNAME, ADMIN_ALIAS, ADMIN_PASSWORD, ADMIN_EMAIL, ADMIN_PHONE) VALUES ('%s', '%s', '%s', '%s', '%s' , '%s')", fName, surname, userName, pass, email, userPhone);
                    //String sqlStatement = "INSERT INTO USER (USER_ID, USER_FIRSTNAME, USER_LASTNAME, USER_ALIAS, USER_PASSWORD, USER_EMAIL) VALUES (3, 'Soo', 'Sohn', 'sysohnn', 'pass', 'sysognatgmail')";
                    String dbURL = "jdbc:mysql://jllscohair.db.11636855.hostedresource.com:3306/jllscohair";
                    Class.forName("com.mysql.jdbc.Driver");
                    connect = DriverManager.getConnection(dbURL, "jllscohair", "Passw0rd132!");
                    Statement statement = connect.createStatement();
                    statement.executeUpdate(sqlStatement);
        }       catch (Exception ex) {
                     System.err.println(ex.getMessage());;
        }       finally {
                 try {
                    if (connect != null) {
                    connect.close();
                       }
                    } catch (SQLException e) {
                 System.err.println(e);
                    }

        }
    }
}
